using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using dbm2.Model;
using dbm2.Model.Types;

namespace dbm2.Syncronizers
{
    public class MSSyncronizer : Syncronizer
    {
        private SqlConnection connection;
        private SqlDataReader reader;

        public MSSyncronizer(SqlConnection connection, DBModel model): base(model)
        {
            this.connection = connection;
        }

        protected override void ConnectionOpen()
        {
            connection.Open();
        }

        protected override void ConnectionClose()
        {
            connection.Close();
        }

        protected override void LoadTables()
        {
            using(SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] LIKE 'BASE TABLE'";
                using (reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                    {
                        Table table = model.UsingSchema ? new Table(reader[2].ToString(),reader[1].ToString()) :
                                                        new Table(reader[2].ToString());
                        model.AddTable(table);
                    }
                }
            }
        }

        protected override void LoadFields()
        {
            using(SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS";
                using(reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                    {
                        Table table = model.GetTable(reader["TABLE_NAME"].ToString(), reader["TABLE_SCHEMA"].ToString());
                        if (table != null) 
                            table.AddField(CreateField());
                    }
                }
            }

            LoadPKS();
            LoadFKS();
        }

        private void LoadFKS()
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"SELECT DISTINCT Fk.name, c2.TABLE_SCHEMA, c2.TABLE_NAME, c2.COLUMN_NAME, 
		                        o.name as PKTABLE, c.COLUMN_NAME as PKCOLUMN
                        FROM INFORMATION_SCHEMA.COLUMNS c, 
                             sys.foreign_keys FK INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c2 
                             ON FK.name = c2.CONSTRAINT_NAME
	                        inner join sys.objects o on o.object_id = FK.referenced_object_id
                        WHERE FK.type = 'F' AND c.COLUMN_NAME = c2.COLUMN_NAME";
                using (reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                    {
                        Field pkField =
                            model.GetTable(reader["PKTABLE"].ToString(), reader["TABLE_SCHEMA"].ToString()).GetField(
                                reader["PKCOLUMN"].ToString());
                        Field fkField =
                            model.GetTable(reader["TABLE_NAME"].ToString(), reader["TABLE_SCHEMA"].ToString()).GetField(
                                reader["COLUMN_NAME"].ToString());

                        fkField.Constraint.SetFKField(pkField, reader["name"].ToString());
                    }
                }
            }
        }

        private void LoadPKS()
        {
            using(SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText =
                    @"SELECT name, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
                        FROM [Chinook].[sys].[key_constraints] PKEYS INNER JOIN [Chinook].[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] PKEYSC
                        ON PKEYS.name = PKEYSC.CONSTRAINT_NAME";
                using(reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                    {
                        Field field =
                            model.GetTable(reader["TABLE_NAME"].ToString(), reader["TABLE_SCHEMA"].ToString()).GetField(
                                reader["COLUMN_NAME"].ToString());
                        field.Constraint.IsPK = true;
                        field.Constraint.PKName = reader["name"].ToString();
                    }
                }
            }
        }

        private Field CreateField()
        {
            Field field = new Field(reader["COLUMN_NAME"].ToString());
            field.Type = new DBMType(reader["DATA_TYPE"].ToString());

            LoadTypeInfo(field);
            LoadConstraints(field);
            return field;
        }

        private void LoadConstraints(Field field)
        {
            field.Constraint.IsNullable = (reader["IS_NULLABLE"].Equals("YES") ? true : false);
        }


        private void LoadTypeInfo(Field field)
        {
            field.Type.Lenght = LoadIntFieldTypeInfo("CHARACTER_MAXIMUM_LENGTH");
            field.Type.Precision = LoadIntFieldTypeInfo("NUMERIC_PRECISION");
            field.Type.Scale = LoadIntFieldTypeInfo("NUMERIC_SCALE");
            field.Type.CollationName = LoadStringFieldTypeInfo("COLLATION_NAME");
            field.Type.CharacterSetName = LoadStringFieldTypeInfo("CHARACTER_SET_NAME");
        }

        private int LoadIntFieldTypeInfo(string name)
        {
            return string.IsNullOrEmpty(reader[name].ToString())
                                       ? int.MinValue
                                       : int.Parse(reader[name].ToString());
        }

        private string LoadStringFieldTypeInfo(string name)
        {
            return string.IsNullOrEmpty(reader[name].ToString())
                       ? null
                       : reader[name].ToString();
        }

        protected override void LoadViews()
        {
            using(SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.VIEWS";
                using (reader = cmd.ExecuteReader())
                {
                    while (reader.HasRows && reader.Read())
                    {
                        View view = new View(reader["TABLE_NAME"].ToString(), reader["TABLE_SCHEMA"].ToString());
                        view.SetDefinition(reader["VIEW_DEFINITION"].ToString());
                        model.AddView(view);
                    }
                }
            }
        }

        protected override void LoadIndexes()
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                foreach (var table in model.GetTables())
                {
                    cmd.CommandText = "exec sp_helpindex " + table.Name;
                    using (reader = cmd.ExecuteReader())
                    {
                        while(reader.HasRows && reader.Read())
                        {
                            Index index = new Index(reader["index_name"].ToString());
                            table.AddIndex(index);

                            foreach(string fieldName in GetFieldsNames(reader["index_keys"].ToString()))
                                index.AddField(table.GetField(fieldName.Trim()));
                            
                            index.Description = reader["index_description"].ToString();
                        }
                    }
                }
            }
        }

        private string[] GetFieldsNames(string indexFields)
        {
            return indexFields.Split(new char[] {','});
        }
    }
}